﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data.SqlClient;
using System.Data;
using System.Configuration;
using System.Text;

namespace RedemptoristRetreatCenter.DatabaseUtility
{

    public class RetreatScheduleUtility
    {
        private SqlConnection rrcDbConn;

        public RetreatScheduleUtility()
        {
            rrcDbConn = new SqlConnection();
            rrcDbConn.ConnectionString = ConfigurationManager.ConnectionStrings["DefaultConnection"].ConnectionString;
        }

        /// <summary>
        /// open connection to database
        /// </summary>
        private void openConnection()
            {
                try
                {
                    rrcDbConn.Open();
                    Console.WriteLine(Environment.NewLine + "rrcDbConn.State: " + rrcDbConn.State.ToString());

                }
                catch (Exception ex)
                {
                    Console.WriteLine("Error opening connecdtion: " + ex.ToString());
                }
            }

        /// <summary>
        ///  close connection to database
        /// </summary>
        public void closeConnection()
        {
            rrcDbConn.Close();
            Console.WriteLine("rrcDbConn.State: " + rrcDbConn.State.ToString());
        }

        /// <summary>
        /// retrieve scheduled retreat for passed in schedule id
        /// </summary>
        /// <param name="scheduleId"></param>
        /// <returns>scheduled retreat data</returns>
        public RetreatScheduleInfo getRetreatScheduleByScheduleId(int scheduleId)
        {
            StringBuilder cmdText = new StringBuilder("SELECT sch.scheduleId, ret.retreatId, ret.title as retreatTitle, ret.description as retreatDescription, ret.fee as retreatFee, sch.directorId, rtrim(usr.firstName) + ' ' + rtrim(usr.lastName) as directorName, sch.startTimestamp, sch.endTimestamp ");
            cmdText.Append("FROM RetreatSchedule sch inner join ");
            cmdText.Append("Retreat ret on sch.retreatId=ret.retreatId left join ");
            cmdText.Append("UserAccount usr on sch.directorId=usr.userId ");
            cmdText.Append(String.Format("WHERE sch.scheduleId={0} and sch.activeData=1;", scheduleId));

            SqlCommand sqlCmd = new SqlCommand(cmdText.ToString(), rrcDbConn);

            List<RetreatScheduleInfo> scheduledRetreats = retrieveScheduledRetreats(sqlCmd);

            if (scheduledRetreats != null && scheduledRetreats.Count > 0)
            {
                return scheduledRetreats[0];  // since scheduleId is unique, only one row will be returned
            }
            else
            {
                return null;
            }
        }

        /// <summary>
        /// retrieve all active scheduled retreats that match list of schedule ids passed in
        /// </summary>
        /// <param name="scheduleIdList"></param>
        /// <returns>list of info for scheduled retreats</returns>
        public List<RetreatScheduleInfo> getRetreatSchedulesByScheduleList(string scheduleIdList)
        {
            string[] scheduleIdArray = scheduleIdList.Split(',');

            StringBuilder cmdText = new StringBuilder("SELECT sch.scheduleId, ret.retreatId, ret.title as retreatTitle, ret.description as retreatDescription, ret.fee as retreatFee, sch.directorId, rtrim(usr.firstName) + ' ' + rtrim(usr.lastName) as directorName, sch.startTimestamp, sch.endTimestamp ");
            cmdText.Append("FROM RetreatSchedule sch inner join ");
            cmdText.Append("Retreat ret on sch.retreatId=ret.retreatId left join ");
            cmdText.Append("UserAccount usr on sch.directorId=usr.userId ");
            cmdText.Append("WHERE sch.scheduleId in (");
            
            foreach (string schId in scheduleIdArray)
            {
                cmdText.Append(String.Format("'{0}',",schId));
            }

            cmdText.Length = cmdText.Length - 1;  // remove last comma
            cmdText.Append(") and sch.activeData=1;");

            SqlCommand sqlCmd = new SqlCommand(cmdText.ToString(), rrcDbConn);

            return retrieveScheduledRetreats(sqlCmd);
        }

        /// <summary>
        /// retrieve all active scheduled retreats for the retreat id passed in
        /// </summary>
        /// <param name="retreatId"></param>
        /// <returns>list of info for scheduled retreats</returns>
        public List<RetreatScheduleInfo> getRetreatSchedulesByRetreatId(int retreatId)
        {
            StringBuilder cmdText = new StringBuilder("SELECT sch.scheduleId, ret.retreatId, ret.title as retreatTitle, ret.description as retreatDescription, ret.fee as retreatFee, sch.directorId, rtrim(usr.firstName) + ' ' + rtrim(usr.lastName) as directorName, sch.startTimestamp, sch.endTimestamp ");
            cmdText.Append("FROM RetreatSchedule sch inner join ");
            cmdText.Append("Retreat ret on sch.retreatId=ret.retreatId left join ");
            cmdText.Append("UserAccount usr on sch.directorId=usr.userId ");
            cmdText.Append(String.Format("WHERE sch.retreatId={0} and sch.activeData=1;", retreatId));

            SqlCommand sqlCmd = new SqlCommand(cmdText.ToString(), rrcDbConn);

            return retrieveScheduledRetreats(sqlCmd);

        }

        /// <summary>
        /// get all active scheduled retreats
        /// </summary>
        /// <returns>list of info for scheduled retreats</returns>
        public List<RetreatScheduleInfo> getAllRetreatSchedules()
        {
            StringBuilder cmdText = new StringBuilder("SELECT sch.scheduleId, ret.retreatId, ret.title as retreatTitle, ret.description as retreatDescription, ret.fee as retreatFee, sch.directorId, rtrim(usr.firstName) + ' ' + rtrim(usr.lastName) as directorName, sch.startTimestamp, sch.endTimestamp ");
                cmdText.Append("FROM RetreatSchedule sch inner join ");
                cmdText.Append("Retreat ret on sch.retreatId=ret.retreatId left join ");
                cmdText.Append("UserAccount usr on sch.directorId=usr.userId ");
                cmdText.Append("WHERE sch.activeData = 1; ");

                SqlCommand sqlCmd = new SqlCommand(cmdText.ToString(), rrcDbConn);

                return retrieveScheduledRetreats(sqlCmd);
        }

        /// <summary>
        /// retieve scheduled retreats from database using the sql command object passed in
        /// </summary>
        /// <param name="sqlCmd"></param>
        /// <returns>list of scheduled retreats</returns>
        private List<RetreatScheduleInfo> retrieveScheduledRetreats(SqlCommand sqlCmd)
        {
            if (rrcDbConn.State != ConnectionState.Open)
            {
                openConnection();
            }

            List<RetreatScheduleInfo> schedules = new List<RetreatScheduleInfo>();

            try
            {
                SqlDataReader retreatScheduleReader = null;
                retreatScheduleReader = sqlCmd.ExecuteReader();
                RetreatScheduleInfo retreatScheduleInfo = null;

                while (retreatScheduleReader.Read())
                {
                    retreatScheduleInfo = new RetreatScheduleInfo();
                    retreatScheduleInfo.scheduleId = (int)retreatScheduleReader["scheduleId"];
                    retreatScheduleInfo.retreatId = (int)retreatScheduleReader["retreatId"];
                    retreatScheduleInfo.retreatTitle = retreatScheduleReader["retreatTitle"].ToString();
                    retreatScheduleInfo.retreatDescription = retreatScheduleReader["retreatDescription"].ToString();
                    retreatScheduleInfo.directorId = (int)retreatScheduleReader["directorId"];
                    retreatScheduleInfo.directorName = retreatScheduleReader["directorName"].ToString();
                    retreatScheduleInfo.startTimestamp = (DateTime)retreatScheduleReader["startTimestamp"];
                    retreatScheduleInfo.endTimestamp = (DateTime)retreatScheduleReader["endTimestamp"];
                    retreatScheduleInfo.retreatFee = (double)retreatScheduleReader["retreatFee"];

                    schedules.Add(retreatScheduleInfo);

                }
            }
            catch (Exception ex)
            {
                // TODO: 
            }
            finally
            {
                closeConnection();
            }
            return schedules;
        }

        /// <summary>
        /// insert scheduled retreat data into database
        /// </summary>
        /// <param name="retreatScheduleInfo"></param>
        /// <param name="username"></param>
        /// <returns></returns>
        public bool insertRetreatSchedule(RetreatScheduleInfo retreatScheduleInfo, string username)
        {

            if (rrcDbConn.State != ConnectionState.Open)
            {
                openConnection();
            }

            int rowsAffected = 0;

            try
            {
                string insertText = String.Format("INSERT INTO RetreatSchedule (retreatId, directorId, startTimestamp, endTimestamp, modifiedBy, modifiedTimestamp, activeData) " +
                        "Values ({0},{1},'{2}','{3}','{4}','{5}', 1);", retreatScheduleInfo.retreatId, retreatScheduleInfo.directorId, retreatScheduleInfo.startTimestamp, retreatScheduleInfo.endTimestamp, username, DateTime.Now.ToString());
                SqlCommand sqlCmd = new SqlCommand(insertText, rrcDbConn);

                rowsAffected = sqlCmd.ExecuteNonQuery();

            }
            catch (Exception ex)
            {
                Console.WriteLine("Failed to insert schedule.  Exception=" + ex.ToString());
                rowsAffected = -1;
            }
            finally
            {
                closeConnection();
            }

            return (rowsAffected > 0);
          }

        /// <summary>
        /// update scheduled retreat data
        /// </summary>
        /// <param name="retreatScheduleInfo"></param>
        /// <param name="username"></param>
        /// <returns>success/failure of insert</returns>
        public bool updateRetreatSchedule(RetreatScheduleInfo retreatScheduleInfo, string username)
        {
            if (rrcDbConn.State != ConnectionState.Open)
            {
                openConnection();
            }

            int rowsAffected = 0;

            try
            {
                string cmdText = String.Format("UPDATE RetreatSchedule SET(retreatId='{0}', directorId='{1}', startTimestamp={2}, endTimestamp={3}, modifiedBy='{4}', modifiedTimestamp='{5}') " +
                    "WHERE scheduleId={6};", retreatScheduleInfo.retreatId, retreatScheduleInfo.directorId, retreatScheduleInfo.startTimestamp, retreatScheduleInfo.endTimestamp, username, DateTime.Now, retreatScheduleInfo.scheduleId);
                SqlCommand sqlCmd = new SqlCommand(cmdText, rrcDbConn);

                rowsAffected = sqlCmd.ExecuteNonQuery();

            }
            catch (Exception ex)
            {
                Console.WriteLine("Failed to update.  Exception=" + ex.ToString());
                rowsAffected = -1;
            }
            finally
            {
                closeConnection();
            }

            return (rowsAffected > 0);
        }

        /// <summary>
        /// delete a scheduled retreat.  This will be a soft delete where the active flag is updated to false
        /// </summary>
        /// <param name="scheduleId"></param>
        /// <param name="username"></param>
        /// <returns></returns>
        public bool deleteRetreatSchedule(int scheduleId, string username)
        {
            if (rrcDbConn.State != ConnectionState.Open)
            {
                openConnection();
            }

            int rowsAffected = 0;

            try
            {
                string insertText = String.Format("UPDATE RetreatSchedule SET activeData=0, modifiedBy='{0}', modifiedTimestamp='{1}' WHERE scheduleId={2};", username, DateTime.Now, scheduleId);
                SqlCommand sqlCmd = new SqlCommand(insertText, rrcDbConn);

                rowsAffected = sqlCmd.ExecuteNonQuery();

            }
            catch (Exception ex)
            {
                Console.WriteLine("Failed to update.  exception=" + ex.ToString());
                rowsAffected = -1;
            }
            finally
            {
                closeConnection();
            }

            return (rowsAffected > 0);
        }

        public List<RetreatScheduleInfo> searchByTitle(String title) 
        {
            if (rrcDbConn.State != ConnectionState.Open)
            {
                openConnection();
            }
            StringBuilder cmdText;
            SqlCommand sqlCmd = null;
            try
            {
                cmdText = new StringBuilder("SELECT sch.scheduleId, ret.retreatId, ret.title as retreatTitle, ret.description as retreatDescription, ret.fee as retreatFee, sch.directorId, rtrim(usr.firstName) + ' ' + rtrim(usr.lastName) as directorName, sch.startTimestamp, sch.endTimestamp ");
                cmdText.Append("FROM RetreatSchedule sch inner join ");
                cmdText.Append("Retreat ret on sch.retreatId=ret.retreatId left join ");
                cmdText.Append("UserAccount usr on sch.directorId=usr.userId ");
                cmdText.Append("WHERE ret.title LIKE '%" + title + "%';");

                sqlCmd = new SqlCommand(cmdText.ToString(), rrcDbConn);
            }
            catch (Exception ex)
            {
                Console.WriteLine("Failed to update.  Exception=" + ex.ToString());
            }
            finally
            {
                closeConnection();
            }

            return retrieveScheduledRetreats(sqlCmd);

        }

        public List<RetreatScheduleInfo> searchByDateRange(DateTime startDate, DateTime endDate) {
            if (rrcDbConn.State != ConnectionState.Open)
            {
                openConnection();
            }
            StringBuilder cmdText;
            SqlCommand sqlCmd = null;
            try
            {
                cmdText = new StringBuilder("SELECT sch.scheduleId, ret.retreatId, ret.title as retreatTitle, ret.description as retreatDescription, ret.fee as retreatFee, sch.directorId, rtrim(usr.firstName) + ' ' + rtrim(usr.lastName) as directorName, sch.startTimestamp, sch.endTimestamp ");
                cmdText.Append("FROM RetreatSchedule sch inner join ");
                cmdText.Append("Retreat ret on sch.retreatId=ret.retreatId left join ");
                cmdText.Append("UserAccount usr on sch.directorId=usr.userId ");
                cmdText.Append("WHERE sch.startTimestamp <= '" + endDate +"' AND sch.endTimestamp >= '" + startDate + "';");

                sqlCmd = new SqlCommand(cmdText.ToString(), rrcDbConn);
            }
            catch (Exception ex)
            {
                Console.WriteLine("Failed to update.  Exception=" + ex.ToString());
            }
            finally
            {
                closeConnection();
            }

            return retrieveScheduledRetreats(sqlCmd);
        }

        public List<RetreatScheduleInfo> searchByTitleAndDateRange(String title, DateTime startDate, DateTime endDate) {
            if (rrcDbConn.State != ConnectionState.Open)
            {
                openConnection();
            }
            StringBuilder cmdText;
            SqlCommand sqlCmd = null;
            try
            {
                cmdText = new StringBuilder("SELECT sch.scheduleId, ret.retreatId, ret.title as retreatTitle, ret.description as retreatDescription, ret.fee as retreatFee, sch.directorId, rtrim(usr.firstName) + ' ' + rtrim(usr.lastName) as directorName, sch.startTimestamp, sch.endTimestamp ");
                cmdText.Append("FROM RetreatSchedule sch inner join ");
                cmdText.Append("Retreat ret on sch.retreatId=ret.retreatId left join ");
                cmdText.Append("UserAccount usr on sch.directorId=usr.userId ");
                cmdText.Append("WHERE ret.title LIKE '%" + title + "%' AND sch.startTimestamp <= '" + endDate + "' AND sch.endTimestamp >= '" + startDate + "';");

                sqlCmd = new SqlCommand(cmdText.ToString(), rrcDbConn);
            }
            catch (Exception ex)
            {
                Console.WriteLine("Failed to update.  Exception=" + ex.ToString());
            }
            finally
            {
                closeConnection();
            }

            return retrieveScheduledRetreats(sqlCmd);
        }

    }
}